package cn.schina.dbfw.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import cn.schina.dbfw.dao.DaoConn;
import cn.schina.dbfw.dao.JDBCConnection;

public class DBWithDbfwDao {
	JDBCConnection conn = null;
	ArrayList<DBWithDbfw> dbwithdbfwArray = new ArrayList<DBWithDbfw>();
	public ArrayList<DBWithDbfw> getDBWithDbfw() throws SQLException
	{
		conn = DaoConn.createDBConnection();
		PreparedStatement statement = conn.getConnection()
			.prepareStatement("select t1.*,IFNULL(baseline.baseline_id,0) baselindid from" +
					"(select dbfw.instance_id dbfw_instid,dbfw.name dbfw_name,dbfw.address dbfw_address," +
					"db.database_id,db.name db_name,db.db_type,db.db_version,db.dialect, " +
					"db_addr.address db_address,db_addr.port db_port,db_addr.service_name db_service_name "+
					"from xsec_databases db,dbfw_fordb,dbfw_instances dbfw,database_addresses db_addr " +
					"where db.database_id=dbfw_fordb.database_id and " +
					"dbfw.instance_id=dbfw_fordb.dbfw_inst_id and " +
					"db_addr.database_id=db.database_id and "+
					"dbfw_fordb.state='1' order by database_id" +
					") t1 left join (actived_baselines baseline) " +
					"on t1.dbfw_instid=baseline.dbfw_inst_id and " +
					"t1.database_id=baseline.db_id and baseline.state=1"
					);
		ResultSet resultSet = statement.executeQuery();
		DBWithDbfw db = new DBWithDbfw();
		db.setId(0);
		while(resultSet.next())
		{
			DBWithDbfw _dbwithdbfw = new DBWithDbfw();
			Dbfw _dbfw = new Dbfw();
			_dbwithdbfw.setId(resultSet.getInt("database_id"));
			_dbwithdbfw.setName_forprotected(resultSet.getString("db_name"));
			_dbwithdbfw.setName(resultSet.getString("db_service_name"));
			_dbwithdbfw.setDbtype(resultSet.getString("db_type"));
			_dbwithdbfw.setDbversion(resultSet.getString("db_version"));
			_dbwithdbfw.setDialect(resultSet.getInt("dialect"));
			_dbwithdbfw.setDb_address(resultSet.getString("db_address"));
			_dbwithdbfw.setDb_port(resultSet.getInt("db_port"));
			if(db.getId()==_dbwithdbfw.getId())
			{
				//相同的DB
				_dbfw.setInstid(resultSet.getInt("dbfw_instid"));
				_dbfw.setName(resultSet.getString("dbfw_name"));
				_dbfw.setAddress(resultSet.getString("dbfw_address"));
				_dbfw.setBaselineid(resultSet.getInt("baselindid"));
				db.addDbfwForDB(_dbfw);
			}
			else
			{
				//新DB记录
				if(db.getId()>0)
				{
					dbwithdbfwArray.add(db);
				}
				db = _dbwithdbfw;
				_dbfw.setInstid(resultSet.getInt("dbfw_instid"));
				_dbfw.setName(resultSet.getString("dbfw_name"));
				_dbfw.setAddress(resultSet.getString("dbfw_address"));
				db.addDbfwForDB(_dbfw);
			}
		}
		if(db.getId()>0)
		{
			dbwithdbfwArray.add(db);
		}
		statement.close();
		return dbwithdbfwArray;
	}
	
	public static void main(String[] args) {
		ArrayList<DBWithDbfw> dbwithdbfwArray = new ArrayList<DBWithDbfw>();
		DBWithDbfwDao _dbwithdbfw;
		_dbwithdbfw = new DBWithDbfwDao();
		try {
			dbwithdbfwArray = _dbwithdbfw.getDBWithDbfw();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
